Autonomous transactions in PL/SQL

Autonomous transactions in PL/SQL

The purpose of autonomous transactions is to allow certain operations to be performed without affecting the outcome of the main transaction. For example, you might want to log errors or audit trail information during the execution of a transaction, without rolling back the entire transaction if an error occurs.

Here’s an example of how to use an autonomous transaction in PL/SQL:

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION; -- declare the transaction as autonomous
BEGIN
  -- perform database operations here
  INSERT INTO audit_trail (user_id, action)
  VALUES (123, 'logged in');
  
  -- commit the autonomous transaction
  COMMIT;
END;

In this example, we are using an autonomous transaction to insert a record into an audit trail table. The PRAGMA AUTONOMOUS_TRANSACTION statement is used to declare the transaction as autonomous. After the database operation is complete, the COMMIT statement is used to commit the changes made in the autonomous transaction, without affecting the outcome of the main transaction.

It’s important to note that autonomous transactions can have some performance implications, as they require additional resources to manage. Therefore, it’s a good practice to use autonomous transactions only when necessary, and to keep them as short and efficient as possible.

Apply for PL/SQL Certification

https://www.vskills.in/certification/certified-pl-sql-developer

Back to Tutorials